const express = require('express');
const app = express();
let url = require('url');
//跨域请求
app.all('*', function(req, res, next) {
    res.header("Access-Control-Allow-Origin", "*");
    res.header("Access-Control-Allow-Headers", "content-type,content-length, authorization,\'origin\',X-Requested-With");
    res.header("Access-Control-Allow-Methods","PUT,POST,GET,DELETE,OPTIONS");
    res.header('Access-Control-Allow-Credentials', true);
    res.header("X-Powered-By",' 3.2.1')
    res.header("Content-Type", "application/json;charset=utf-8");
    next();
});

//添加数据库模板依赖s
const models = require('../../db/models');
const {Op} = require("sequelize");//注入Op，用于多条件查询等等
const {QueryTypes} = require("sequelize");
const bodyParser = require('body-parser');
const path = require("path");
//时间格式设置所需要组件
//引入组件
app.use(bodyParser.urlencoded({extended:false}));
app.use(bodyParser.json());
app.use(express.static(path.join(__dirname,'public')));

//页面的入口，跳转到index页面，然后通过钩子函数，查询数据渲染页面
app.get('/login',function (req,res){
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/index.html");
})
//跳转course.html页面
app.get('/toCourse',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/course.html");
})
//跳转choice.html页面
app.get('/toChoice',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/choice.html");
})
//跳转detail.html页面
app.get('/toDetail',function (req,res) {
    res.type("html");
    res.sendFile("D:/Workspaces/IdeaWorkspaces/StudentSys/src/detail.html");
})
//刷新course.html页面数据
app.post('/show',async function (req,res){
    let cols = req.body.data.cols;
    let limit = req.body.data.limit;
    let offset = (cols-1)*limit;
    //重新查询数据该学生对应的课程信息返回页面
    let retJoin = [];
    //获得数据总行数
    let sum = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id",{
        type:models.Sequelize.QueryTypes.SELECT,
    })
    retJoin = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id limit ?,?",{
        replacements:[offset,limit],
        type:models.Sequelize.QueryTypes.SELECT,
    })
    let count = sum.push();
    res.json({
        retJoin,
        count
    })
})
//模糊查询，分页查询
app.post('/query',async function (req,res){
    let getVal = req.body.data.val;
    let cols = req.body.data.cols;
    let limit = req.body.data.limit;
    let offset = (cols-1)*limit;
    //重新查询数据该学生对应的课程信息返回页面
    let retJoin = [];
    //获得数据总行数
    let sum = "";
    if (getVal==""){
        sum = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id",{
            replacements:[getVal],
            type:models.Sequelize.QueryTypes.SELECT,
        })
        retJoin = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id limit ?,?",{
            replacements:[offset,limit],
            type:models.Sequelize.QueryTypes.SELECT,
        })
    }else{
        sum = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id where s.id=?",{
            replacements:[getVal],
            type:models.Sequelize.QueryTypes.SELECT,
        })
        retJoin = await models.sequelize.query("SELECT s.id sid,c.id cid,c.name,c.credit,c.period,c.remark from stu_students s INNER JOIN studentjoincourses j on s.id=j.sid inner JOIN stu_courses c on j.cid=c.id where s.id=? limit ?,?",{
            replacements:[getVal,offset,limit],
            type:models.Sequelize.QueryTypes.SELECT,
        })
    }
    let count = sum.push();
    res.json({
        retJoin,
        count
    })
})
//异常处理
function error_handler(err,req,res,next){
    if (err){
        let {message} = err;
        res.status(500).json({
            message:message || "服务器异常"
        })
    }
}

app.use(error_handler);

//服务监听
app.listen(3003, ()=>{
    console.log("启动成功detail!")
})